package net.phpress.bbs;

import java.util.ArrayList;
import java.util.List;

import net.phpress.db.DBConn;

public class BBSDAO extends DBConn {

	public BBSDAO() {
		Connection();
	}

	// 1. num의 max값
	public int getMaxNum() {
		int result = 0;
		String sql;
		try {
			sql = "select nvl(max(no),0) from bbs";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				result = rs.getInt(1);
			}
			closeConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	// 2. 입력
	public int insertData(BBSDTO dto) {
		int result = 0;
		String sql;
		try {
			sql = "insert into bbs(no, title, name, wtime, content)"
					+ " values(?,?,?,systimestamp,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getNo());
			pstmt.setString(2, dto.getTitle());
			pstmt.setString(3, dto.getName());
			pstmt.setString(4, dto.getContent());
			result = pstmt.executeUpdate();
			closeConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	public List<BBSDTO> getLists(int start, int end, String searchKey,
			String searchValue) {
		List<BBSDTO> lists = new ArrayList<BBSDTO>();
		String sql;
		try {
			searchValue = "%" + searchValue + "%";
			// select * from ( select rownum rnum, data.* from (select
			// num,name,subject,hitCount,to_char(created,'YYYY-MM-DD') created
			// from board order by num desc) data)
			sql = "select * from ( " + "select rownum rnum, data.* from ("
					+ "select no, title, name, "
					+ "to_char(wtime,'YYYY-MM-DD HH:mm:ss') created, content "
					+ "from bbs where " + searchKey
					+ " like ? order by num desc" + ") data"
					+ ") where rnum >= ? and rnum <= ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, searchValue);
			pstmt.setInt(2, start);
			pstmt.setInt(3, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BBSDTO dto = new BBSDTO();
				dto.setNo(rs.getInt(2));
				dto.setTitle(rs.getString(3));
				dto.setName(rs.getString(4));
				dto.setWtime(rs.getString(5));
				dto.setContent(rs.getString(6));
				lists.add(dto);
			}
			closeConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return lists;
	}

	// 4. 전체 레코드의 개수
	public int getDataCount(String searchKey, String searchValue) {
		int result = 0;
		String sql;
		try {
			searchValue = "%" + searchValue + "%";
			sql = "select nvl(count(*),0) from bbs where ? like ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, searchKey);
			pstmt.setString(2, searchValue);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			closeConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	// 5. num으로 한 사람의 데이터를 가져오기
	public BBSDTO getReadData(int no) {
		BBSDTO dto = null;
		String sql;
		try {
			sql = "select no, title, name, "
					+ "to_char(wtime,'YYYY-MM-DD HH:mm:ss') created, content "
					+ "from bbs where no=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, no);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				dto = new BBSDTO();
				dto.setNo(rs.getInt(1));
				dto.setTitle(rs.getString(2));
				dto.setName(rs.getString(3));
				dto.setWtime(rs.getString(4));
				dto.setContent(rs.getString(5));
			}
			closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dto;
	}

	// 8. 삭제
	public int deleteData(int num) {
		int result = 0;
		String sql;
		try {
			sql = "delete from bbs where no=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			result = pstmt.executeUpdate();
			closeConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}
}
